#!/usr/bin/env python3
# -*- coding:utf-8 -*-

import pymysql

# 统计打卡和未打卡人数并写入txt文档中


def get_loan_number(file):
    connect = pymysql.Connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        password='wy123456',
        db='code_recoad',
        charset='utf8'
    )
    cursor = connect.cursor()
    sql = """select recode2.stu_id,stu.stu_name,recode2.time 
        from recode2,stu
        where 
           recode2.stu_id=stu.stu_id
        """
    sum = 0  # 统计已打卡人数
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        fp = open(file, "w")
        results = cursor.fetchall()
        fp.write("            学生打卡记录表1\n")
        fp.write("-----------------------------------\n")
        fp.write("学号" + "\t" + "姓名" + "\t" + "打卡时间" + "\n")
        for it in results:
            sum += 1
            fp.write(it[0] + "\t")
            fp.write(it[1] + "\t")
            fp.write(it[2] + "\n")
        fp.write("------------------------------------\n")
        fp.write("总计：%d人已签到\n" % sum)
        print("打印完成！\n已签到%d人\n" % sum)
    except:
        print("未查询到数据！")
    # 查询缺勤人员名单
    sql2 = "select * from stu where stu_id not in (select stu_id from recode2)"
    cursor.execute(sql2)
    number = cursor.fetchall()
    fp = open(file, "a")
    loan_count = 0   # 统计未打卡人数
    fp.write("\n      缺勤记录表2  \n")
    fp.write("---------------------------------------\n")
    fp.write("学号" + "\t" + "姓名" + "\t" + "班级" + "\n")
    for loanNumber in number:
        loan_count += 1
        fp.write(loanNumber[0] + "\t")
        fp.write(loanNumber[1] + "\t")
        fp.write(loanNumber[2] + "\n")
    fp.write("--------------------------------------\n")
    fp.write("总计：%d人未签到" % loan_count)
    fp.close()
    cursor.close()
    connect.close()
    print("未签到%d人" % loan_count)


if __name__ == "__main__":
    file = r"CheckQRRecord.txt"
    get_loan_number(file)
